﻿// -----------------------------------------------------------------------
// <copyright file="WorkflowData.cs" company="VDC-IT" author="LTDUC">
//      Copyright (c) VDC-IT. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

namespace VDC.VONE.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Reflection;
    using Microsoft.ApplicationBlocks.Data;
    using VDC.COMMON;
    using VDC.VONE.ENTITIES;
    
    /// <summary>
    /// Class access to db Workflow
    /// </summary>
    public class WorkflowData
    {
        /// <summary>
        /// Get Workflow By Id
        /// </summary>
        /// <param name="workflowId">Param workflowId</param>
        /// <returns>Return Workflow</returns>
        public WorkflowEN GetWorkflowById(int workflowId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                WorkflowEN workflow = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneWorkflowGetById, workflowId);
                while (reader.Read())
                {
                    workflow = new WorkflowEN();

                    if (!reader.IsDBNull(0))
                    {
                        workflow.WorkflowId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        workflow.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        workflow.Name = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        workflow.CreateDate = reader.GetDateTime(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        workflow.Descriptions = reader.GetString(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        workflow.Active = reader.GetBoolean(5);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return workflow;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list all Workflow
        /// </summary>
        /// <returns>Return list all Workflow</returns>
        public IList<WorkflowEN> GetWorkflows()
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<WorkflowEN> lstWorkflow = new List<WorkflowEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneWorkflowGetAll);
                while (reader.Read())
                {
                    WorkflowEN workflow = new WorkflowEN();

                    if (!reader.IsDBNull(0))
                    {
                        workflow.WorkflowId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        workflow.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        workflow.Name = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        workflow.CreateDate = reader.GetDateTime(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        workflow.Descriptions = reader.GetString(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        workflow.Active = reader.GetBoolean(5);
                    }

                    lstWorkflow.Add(workflow);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstWorkflow;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Insert to db Workflow
        /// </summary>
        /// <param name="workflow">Paarm Workflow</param>
        /// <returns>Insert Workflow</returns>
        public int Insert(WorkflowEN workflow)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.Code, workflow.Code),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.Name, workflow.Name),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.CreateDate, (object)workflow.CreateDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.Descriptions, workflow.Descriptions),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.Active, workflow.Active)
                                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneWorkflowInsert, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Update to db Workflow
        /// </summary>
        /// <param name="workflow">Param Workflow</param>
        public void Update(WorkflowEN workflow)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.WorkflowId, workflow.WorkflowId),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.Code, workflow.Code),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.Name, workflow.Name),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.CreateDate, (object)workflow.CreateDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.Descriptions, workflow.Descriptions),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.Active, workflow.Active)
                                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneWorkflowUpdate, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Update to db Workflow
        /// </summary>
        /// <param name="workflow">Param Workflow</param>
        public void UpdateAndDelete(WorkflowEN workflow)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.WorkflowId, workflow.WorkflowId),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.Code, workflow.Code),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.Name, workflow.Name),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.CreateDate, (object)workflow.CreateDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.Descriptions, workflow.Descriptions),
                                    new SqlParameter(Constants.SqlParamerterName.Workflow.Active, workflow.Active)
                                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneWorkflowUpdateAndDelete, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }


        /// <summary>
        /// Delete Workflow by workflowId
        /// </summary>
        /// <param name="workflowId">param workflowId</param>
        public void Delete(int workflowId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.Workflow.WorkflowId, workflowId)
                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneWorkflowDelete, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }
        
        public bool Exist(int WorkflowId, string code)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.Workflow.WorkflowId, WorkflowId),
                    new SqlParameter(Constants.SqlParamerterName.Workflow.Code, code)
                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneWorkflowExist, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result > 0;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        public bool IsUsedProfileType(int workflowId, int profileTypeId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneWorkflowUsedProfileType, workflowId, profileTypeId));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result > 0;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }
    }
}